﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtility;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
namespace OracleDAL.Sec
{
    public class User
    {
        /// <summary>
        /// 根据USERISN查询一行数据
        /// </summary>
        /// <param name="UserISN"></param>
        /// <returns>数据行</returns>
        private System.Data.DataRow GetByUserISN(string UserISN)
        {
            string sql = "SELECT * FROM SEC_USERINFO_NEW SU WHERE SU.USERISN = '" + UserISN + "'";
            System.Data.DataSet ds = DBUtility.OracleHelper.Query(sql);
            System.Data.DataRow dr = null;
            if (ds.Tables[0].Rows.Count > 0)
                dr = ds.Tables[0].Rows[0];
            return dr;
        }
        /// <summary>
        /// 删除用户,用户组与用户的对应关系,用户与节点的关系
        /// </summary>
        /// <param name="UserISN">用户ISN</param>
        /// <returns>删除是否成功</returns>
        public bool DeleteUser(string UserISN)
        {
            List<string> tranSql = new List<string>();
            //删除用户
            string sql = "DELETE  FROM SEC_USERINFO_NEW WHERE USERISN = '" + UserISN + "'";
            //删除用户组与用户的对应关系
            string del_UserGroup_UserInfo = "delete from SEC_USERGROUP_USERINFO where USERISN='" + UserISN + "'";
            //删除用户与节点的关系
            string del_User_Node = "delete from SEC_USER_NODE where USERISN='" + UserISN + "'";
            tranSql.Add(sql);
            tranSql.Add(del_UserGroup_UserInfo);
            tranSql.Add(del_User_Node);
            int effectRows = DBUtility.OracleHelper.ExecuteSqlTran(tranSql);
            if (effectRows > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 新增用户
        /// </summary>
        /// <param name="UserISN">用户ISN</param>
        /// <param name="UnitISN">所属单位ISN</param>
        /// <param name="UserName">用户名</param>
        /// <param name="Password">密码</param>
        /// <param name="UnitName">所属单位名称</param>
        /// <param name="CreateTime">创建时间</param>
        /// <param name="Position">职称</param>
        /// <param name="Tel">电话</param>
        /// <param name="EMail">电子邮件地址</param>
        /// <param name="IPSection">允许登录的IP网段</param>
        /// <param name="Sort">显示顺序</param>
        /// <returns>是否成功</returns>
        public bool NewUser(string UserISN, string UnitISN, string UserName, string Password, string UnitName, DateTime CreateTime, string Position, string Tel, string EMail, string IPSection, int Sort)
        {
            string sql = "INSERT INTO SEC_USERINFO_NEW(USERISN,UNITISN,USERNAME,PASSWORD,UNITNAME,CREATETIME,POSITION,TEL,EMAIL,IPSECTION,SORT) ";
            sql += "values ('" + UserISN + "','" + UnitISN + "','" + UserName + "','" + Password + "','" + UnitName + "',to_date('" + CreateTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd hh24:mi:ss'),'" + Position + "','" + Tel + "','" + EMail + "','" + IPSection + "'," + Sort + ")";
            int effectRows = DBUtility.OracleHelper.ExecuteSql(sql);
            if (effectRows > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 更新用户
        /// </summary>
        /// <param name="UserISN">用户ISN</param>
        /// <param name="UnitISN">所属单位ISN</param>
        /// <param name="UserName">用户名</param>
        /// <param name="Password">密码</param>
        /// <param name="UnitName">所属单位名称</param>
        /// <param name="CreateTime">创建时间</param>
        /// <param name="Position">职称</param>
        /// <param name="Tel">电话</param>
        /// <param name="EMail">电子邮件地址</param>
        /// <param name="IPSection">允许登录的IP网段</param>
        /// <param name="Sort">显示顺序</param>
        /// <returns>是否成功</returns>
        public bool UpdateUser(string UserISN, string UnitISN, string UserName, string Password, string UnitName, DateTime CreateTime, string Position, string Tel, string EMail, string IPSection, int Sort)
        {
            string sql = "UPDATE SEC_USERINFO_NEW SET UNITISN='" + UnitISN + "',USERNAME='" + UserName + "',PASSWORD='" + Password + "',UNITNAME='" + UnitName + "',CREATETIME=to_date('" + CreateTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd hh24:mi:ss'),POSITION='" + Position + "',TEL='" + Tel + "',EMAIL='" + EMail + "',IPSECTION='" + IPSection + "',SORT=" + Sort + " WHERE USERISN='" + UserISN + "'";
            int effectRows = DBUtility.OracleHelper.ExecuteSql(sql);
            if (effectRows > 0)
                return true;
            else
                return false;
        }
        public bool UpdateUser(Model.Sec.User user)
        {
            return this.UpdateUser(user.UserISN, user.UnitISN, user.UserName, user.Password, user.UnitName, user.CreateTime, user.Position, user.Tel, user.EMail, user.IPSection, user.Sort);
        }
        public Model.Sec.UserCollection GetOnlineUsers(string UnitISN)
        {
            string sql = "";
            System.Data.DataTable dt = DBUtility.OracleHelper.Query(sql).Tables[0];
            Model.Sec.UserCollection uc = new Model.Sec.UserCollection(dt.Rows.Count);
            for (int rowNum = 0; rowNum < dt.Rows.Count; rowNum++)
            {
                Model.Sec.User user = new Model.Sec.User();

                user.UserISN = dt.Rows[rowNum]["USERISN"].ToString();
                if (dt.Rows[rowNum]["CREATETIME"].ToString() != "")
                    user.CreateTime = Convert.ToDateTime(dt.Rows[rowNum]["CREATETIME"].ToString());
                user.EMail = dt.Rows[rowNum]["EMAIL"].ToString();
                user.IPSection = dt.Rows[rowNum]["IPSECTION"].ToString();
                user.Password = dt.Rows[rowNum]["PASSWORD"].ToString();
                user.Position = dt.Rows[rowNum]["POSITION"].ToString();
                user.Sort = Convert.ToInt32(dt.Rows[rowNum]["SORT"]);
                user.Tel = dt.Rows[rowNum]["TEL"].ToString();
                user.UnitISN = dt.Rows[rowNum]["UNITISN"].ToString();
                user.UnitName = dt.Rows[rowNum]["UNITNAME"].ToString();
                user.UserName = dt.Rows[rowNum]["USERNAME"].ToString();

                //user.PropertyInitialized = true;

                uc[rowNum] = user;
            }
            return uc;
            throw new NotImplementedException("sql语句为空。");
        }
        public List<Model.Sec.UserGroup> GetUserGroups(string UserISN)
        {
            List<Model.Sec.UserGroup> list = new List<Model.Sec.UserGroup>();
            string sql = "SELECT SUG.* FROM SEC_USERGROUP_USERINFO SUU,SEC_USERGROUP SUG WHERE SUU.USERISN='" + UserISN + "' AND SUU.USERGROUPISN=SUG.USERGROUPISN";
            System.Data.OracleClient.OracleDataReader odr = DBUtility.OracleHelper.ExecuteReader(sql);
            while (odr.Read())
            {
                Model.Sec.UserGroup ug = new Model.Sec.UserGroup();
                if (odr["CREATETIME"].ToString() != "")
                    ug.CreateTime = Convert.ToDateTime(odr["CREATETIME"]);
                ug.Description = odr["DESCRIPTION"].ToString();
                ug.SortIndex = odr["SORTINDEX"].ToString();
                ug.UserGroupISN = odr["USERGROUPISN"].ToString();
                ug.UserGroupName = odr["USERGROUPNAME"].ToString();
                list.Add(ug);
            }
            return list;
        }
        //
        public Model.Sec.User GetModel(string UserISN)
        {
            Model.Sec.User user = new Model.Sec.User();

            System.Data.DataRow dr = GetByUserISN(UserISN);
            if (dr != null)
            {
                user.UserISN = dr["USERISN"].ToString();
                if (dr["CREATETIME"].ToString() != "")
                    user.CreateTime = Convert.ToDateTime(dr["CREATETIME"].ToString());
                user.EMail = dr["EMAIL"].ToString();
                user.IPSection = dr["IPSECTION"].ToString();
                user.Password = dr["PASSWORD"].ToString();
                user.Position = dr["POSITION"].ToString();
                if (!string.IsNullOrEmpty(dr["SORT"].ToString()))
                {
                    user.Sort = Convert.ToInt32(dr["SORT"]);
                }
                user.Tel = dr["TEL"].ToString();
                user.UnitISN = dr["UNITISN"].ToString();
                user.UnitName = dr["UNITNAME"].ToString();
                user.UserName = dr["USERNAME"].ToString();
            }

            return user;
        }
        /// <summary>
        /// 更新用户的状态
        /// </summary>
        /// <param name="UserISN">条件</param>
        /// <returns></returns>
        public bool UpdateMyStatus(string UserISN)
        {
            // Model.Sec.User user = new Model.Sec.User();
            string sql = "insert into sec_watchman(lastwatchtime) values('" + System.DateTime.Now + ") where userisn='" + UserISN + "'";
            int effectRows = DBUtility.OracleHelper.ExecuteSql(sql);
            if (effectRows > 0)
                return true;
            else
                return false;
        }
        public bool existUser(string strWhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from sec_userinfo_new  ");
            if (strWhere.Trim() != "")
                sql.Append("where " + strWhere);
            int row = 0;
            OracleDataReader dr = OracleHelper.ExecuteReader(sql.ToString());
            while (dr.Read())
                row++;
            if (row > 0)
                return true;
            else
                return false;
        }
        public bool isRootsGroupUser(string userISN)
        {
            string sql = "select count(1) from sec_usergroup_userinfo where usergroupisn='roots' and userisn='" + userISN + "'";
            return OracleHelper.Exists(sql);

        }
        public int getMaxSort(string strWhere)
        {
            string sql = "select max(sort) from sec_userinfo_new ";
            if (strWhere.Trim() != "")
                sql += "where " + strWhere;
            int max = 0;
            OracleDataReader dr = OracleHelper.ExecuteReader(sql);
            while (dr.Read())
                max++;
            return max;
        }
        /// <summary>
        /// 根据上级的NodeID从单位表中查找用户,排除管理员root
        /// </summary>
        /// <param name="NodeID"></param>
        /// <returns></returns>
        public Model.Sec.User[] getArrUsers(string NodeID)
        {
            ArrayList arr = new ArrayList();

           //不查找root用户
            string sql = "select us.userisn,us.username,us.unitname,us.position,us.tel,us.email,us.ipsection ,us.createtime,us.unitisn from sec_userinfo_new us,sec_unit unit where unit.unitisn=us.unitisn and us.userisn <> 'root' and unit.upnodeid like '" + NodeID + "%'";
      
            OracleDataReader dr = OracleHelper.ExecuteReader(sql);
            while (dr.Read())
            {
                Model.Sec.User user = new Model.Sec.User();
                if (dr["CREATETIME"].ToString() != "")
                    user.CreateTime = Convert.ToDateTime(dr["CREATETIME"].ToString());
                user.EMail = dr["EMAIL"].ToString();
                user.IPSection = dr["IPSECTION"].ToString();
                user.Position = dr["POSITION"].ToString();
                user.Tel = dr["TEL"].ToString();
                user.UserISN = dr["USERISN"].ToString();
                user.UserName = dr["USERNAME"].ToString();
                user.UnitName = dr["UNITNAME"].ToString();
                arr.Add(user);
            }
            Model.Sec.User[] arrUser = (Model.Sec.User[])arr.ToArray(typeof(Model.Sec.User));
            return arrUser;
        }
        /// <summary>
        /// 根据上级的NodeID从单位表中查找用户
        /// </summary>
        /// <param name="NodeID">节点ID</param>
        /// <param name="isRoot">是否是管理员root</param>
        /// <returns></returns>
        public DataTable getDataTable(string NodeID,bool isRoot)
        {
            string sql = "select us.* from sec_userinfo_new us,sec_unit unit where unit.unitisn=us.unitisn and us.userisn <> 'root' and unit.upnodeid like '" + NodeID + "%'";
            if (isRoot)
            {
                sql = "select us.userisn,us.username,us.unitname,us.position,us.tel,us.email,us.ipsection ,us.createtime,us.unitisn from sec_userinfo_new us,sec_unit unit where unit.unitisn=us.unitisn and unit.upnodeid like '" + NodeID + "%'";
            }
            DataTable dt=  OracleHelper.Query(sql).Tables[0];
            return dt;
        }
        
        /// <summary>
        /// 从单位表中查用户
        /// </summary>
        /// <param name="unitisn"></param>
        /// <param name="isRoot"></param>
        /// <returns></returns>
        public DataTable getUserDataTable(string unitisn,bool isRoot)
        {
            string sql = "select us.* from sec_userinfo_new us,sec_unit unit where unit.unitisn=us.unitisn and us.userisn <> 'root' and unit.unitisn= '" + unitisn + "'";
            if (isRoot)
            {
                sql = "select us.* from sec_userinfo_new us,sec_unit unit where unit.unitisn=us.unitisn and unit.unitisn= '" + unitisn + "'";
            }
            DataTable dt = OracleHelper.Query(sql).Tables[0];
            return dt;
        }
        /// <summary>
        /// 返回用户的权限，权限以,号分开
        /// </summary>
        /// <param name="userisn">用户isn</param>
        /// <returns></returns>
        public string getOperateID(string userisn)
        {
            string sql = "select distinct(uo.operateid) from sec_usergroup_operate uo,sec_usergroup_userinfo suu where suu.usergroupisn=uo.usergroupisn and enabled='1' and belongsystem='10' and suu.userisn='" + userisn + "'";
             string OperateID="";
            OracleDataReader dr = OracleHelper.ExecuteReader(sql);
            while (dr.Read())
                OperateID += dr["operateid"].ToString()+",";
            return OperateID;
        }
        /// <summary>
        /// 返回用户在页面的权限OperateID，common
        /// </summary>
        /// <param name="UserISN"></param>
        /// <param name="ParentOperateID"></param>
        /// <returns></returns>
        public DataTable getChildOperateID(string UserISN, string ParentOperateID)
        {
            string sql = "select distinct(operateid),common from sec_operate_new where substr(treeorder,0,length(treeorder)-2)=(select treeorder from sec_operate_new  where  operateid='" + ParentOperateID + "') and operateid in  (select distinct(uo.operateid) from sec_usergroup_operate uo,sec_usergroup_userinfo suu where suu.usergroupisn=uo.usergroupisn and enabled='1' and belongsystem='10' and suu.userisn='" + UserISN + "')";
           
            return OracleHelper.Query(sql).Tables[0];
        }
        /// <summary>
        /// 返回下一级有权限的OPerateID，operatename
        /// </summary>
        /// <param name="UserISN"></param>
        /// <param name="ParentOperateID"></param>
        /// <returns></returns>
        public DataTable getTableChildOperateID(string UserISN, string ParentOperateID)
        {
            //string sql = "select distinct(operateid),operatename from sec_operate_new where substr(treeorder,0,length(treeorder)-2)=(select treeorder from sec_operate_new  where  operateid='" + ParentOperateID + "') and operateid in  (select distinct(uo.operateid) from sec_usergroup_operate uo,sec_usergroup_userinfo suu where suu.usergroupisn=uo.usergroupisn and enabled='1' and belongsystem='10' and suu.userisn='" + UserISN + "')";
            string sql = "select distinct(operateid),operatename from sec_operate_new where substr(treeorder,0,length(treeorder)-2)=(select treeorder from sec_operate_new  where  operateid='" + ParentOperateID + "' and  belongsystem='10')  and  operateid in  (select distinct(uo.operateid) from sec_usergroup_operate uo,sec_usergroup_userinfo suu where suu.usergroupisn=uo.usergroupisn and enabled='1' and belongsystem='10' and suu.userisn='" + UserISN + "')";

            return OracleHelper.Query(sql).Tables[0];
        }
        /// <summary>
        /// 返回下级所有有权限的OPerateID，operatename
        /// </summary>
        /// <param name="UserISN"></param>
        /// <param name="ParentOperateID"></param>
        /// <returns></returns>
        public DataTable getAllChildOperateID(string UserISN, string ParentOperateID)
        {
            string sql = "select distinct(operateid),operatename from sec_operate_new where treeorder like (select treeorder||'__%' from sec_operate_new  where  operateid='" + ParentOperateID + "') and operateid in  (select distinct(uo.operateid) from sec_usergroup_operate uo,sec_usergroup_userinfo suu where suu.usergroupisn=uo.usergroupisn and enabled='1' and belongsystem='10' and suu.userisn='" + UserISN + "')";
            return OracleHelper.Query(sql).Tables[0];
        }
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="PassWord"></param>
        /// <param name="UserISN"></param>
        /// <returns></returns>
        public bool ChangePwd(string PassWord, string UserISN)
        {
            string sql = "UPDATE SEC_USERINFO_NEW SET PASSWORD='" + PassWord + "' where userisn='" + UserISN + "'";
            return OracleHelper.ExecuteSql(sql) > 0 ? true : false;
        }
    }
}
